package org.compiere.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;


public class MhrmvacationscheduleLine extends X_hrm_vacationscheduleLine {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = -3733235991130676336L;
	
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (MhrmvacationscheduleLine.class);

	public MhrmvacationscheduleLine(Properties ctx,
			int hrm_vacationscheduleLine_ID, String trxName) {
		super(ctx, hrm_vacationscheduleLine_ID, trxName);
	}

	public MhrmvacationscheduleLine(Properties ctx, ResultSet rs, String trxName) {
		super(ctx, rs, trxName);
	}
	
	/**
	 * Search for entries in the time sheet
	 * 
	 * @param C_BPartner_ID
	 * @param dt
	 * @return  Model time sheet
	 */
	public static MhrmvacationscheduleLine getVacationscheduleLine(int C_BPartner_ID, int year) {
	
		//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;
		MhrmvacationscheduleLine vaLine = new MhrmvacationscheduleLine(Env.getCtx(), 0, null);
			
		// Employee data from the timesheets
		String sql_ = "SELECT T.* FROM HRM_VACATIONSCHEDULELINE T" +
				" JOIN HRM_VACATIONSCHEDULE R ON R.HRM_VACATIONSCHEDULE_ID=T.HRM_VACATIONSCHEDULE_ID" +
				" WHERE T.ISACTIVE='Y' AND T.AD_CLIENT_ID=? " +
				" AND T.C_BPARTNER_ID=? " +
				" AND R.HRM_YEARS_ID=?";
			try
			{
				pstmt = DB.prepareStatement(sql_,null);
				pstmt.setInt(1, Env.getAD_Client_ID(Env.getCtx()));
				pstmt.setInt(2, C_BPartner_ID);
				pstmt.setInt(3, year);
				rs = pstmt.executeQuery();	
				if(rs.next())
					vaLine = new MhrmvacationscheduleLine(Env.getCtx(), rs, null);
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
		return vaLine;
		
	}
	
	/** Load Model for vacation schedule Line depending on the department*/
    public static MhrmvacationscheduleLine[] getLines (int HR_Department_ID, int hrm_years_ID, Integer BPartner_ID)
    {
    	
    	//
    	MhrmvacationscheduleLine vacLine = null;
    	List<MhrmvacationscheduleLine> list = new ArrayList<MhrmvacationscheduleLine>();
    	//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;		
		
		List<Object> param = new ArrayList<Object>();
		param.add(Env.getAD_Client_ID(Env.getCtx()));
		param.add(hrm_years_ID);
		
		// Employee data from the vacation schedule Line
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT T.* FROM HRM_VACATIONSCHEDULELINE T \n");
		sql.append(" JOIN HRM_VACATIONSCHEDULE R ON R.HRM_VACATIONSCHEDULE_ID=T.HRM_VACATIONSCHEDULE_ID \n");
		sql.append(" WHERE T.ISACTIVE='Y' \n");
		sql.append(" AND T.AD_CLIENT_ID=? \n");
		sql.append(" AND R.HRM_YEARS_ID=? \n");
		
		if(HR_Department_ID != 0){
			sql.append(" AND R.HR_DEPARTMENT_ID=? \n");
			param.add(HR_Department_ID);
		}
		
		if(BPartner_ID != null){
			sql.append(" AND T.C_BPARTNER_ID=? \n");
			param.add(BPartner_ID);
		}
		
		sql.append("ORDER BY T.STARTDATE");
		
		try
			{
				pstmt = DB.prepareStatement(sql.toString(), null);
				DB.setParameters(pstmt, param);
				rs = pstmt.executeQuery();	
				
				while (rs.next ())
				{
					vacLine = new MhrmvacationscheduleLine(Env.getCtx(), rs, null);
					list.add(vacLine);
				}
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
			return list.toArray(new MhrmvacationscheduleLine[list.size()]);
    }
    
 // Error message
 	private StringBuilder errmsg = new StringBuilder();
    
    @Override
    protected boolean beforeSave(boolean newRecord) {
    
    	super.beforeSave(newRecord);
    	
    	if(!newRecord){
    		if(getStartDate() == null) errmsg.append(Util.requiredField(COLUMNNAME_StartDate)).append("\n");
    		if(getEndDate() == null) errmsg.append(Util.requiredField(COLUMNNAME_EndDate)).append("\n");
    		
    		Util.checkErrMsg(errmsg);
    	}else{    	
	    	//
	    	int days = Util.getDays(getStartDate(), getEndDate());;
	    	// check limit vacation days
	    	if((days+getDaysExisting()) > 30)
	    		errmsg.append("Days limit exceeded");
			
			Util.checkErrMsg(errmsg);
    	}
    	    	
    	return true;
    }
    
    private int getDaysExisting(){
    	
    	PreparedStatement pstmt = null;
 		ResultSet rs = null;		
 		int days = 0;
 			
 		// 
 		String sql_ = "SELECT SUM(EXTRACT(DAY FROM AGE(ENDDATE, STARTDATE)))::NUMERIC(2,0) FROM HRM_VACATIONSCHEDULELINE " +
 				"WHERE C_BPARTNER_ID=? " +
 				"AND HRM_VACATIONSCHEDULE_ID=?";
 			try
 			{
 				pstmt = DB.prepareStatement(sql_,null);
 				pstmt.setInt(1, getC_BPartner_ID());
 				pstmt.setInt(2, gethrm_vacationschedule_ID());
 				rs = pstmt.executeQuery();	
 				
 				if(rs.next())
 					days = rs.getInt(1);
 			}
 			catch (SQLException e)
 			{
 				s_log.log(Level.SEVERE, "product", e);
 			}
 			finally
 			{
 				DB.close(rs, pstmt);
 				rs = null; pstmt = null;
 			}	
    	
    	return days;
    }

}
